﻿using ESP.Data;
using ESP.Test.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace ESP.Test.BusinessLogics
{
    public static class UserInfoManager
    {
        public static List<UserInfo> GetList(
           IEnumerable<Expression<Func<UserInfo, bool>>> predicate,
           IEnumerable<Expression<Func<UserInfo, Orderby>>> orderby)
        {
            string sql;
            bool hasPredicate = !predicate.IsEmpty();
            bool hasOrderby = !orderby.IsEmpty();
            PredicateCommandParameters pcp = hasPredicate ? new PredicateCommandParameters("WHERE", predicate) : null;
            OrderbyCommandParameters ocp = hasOrderby ? new OrderbyCommandParameters("ORDERBY", orderby) : null;
            if (hasPredicate && hasOrderby)
                sql = "SELECT * FROM T_UserInfo WHERE @@@WHERE@@@ ORDER BY @@@ORDERBY@@@";
            else if (hasPredicate)
                sql = "SELECT * FROM T_UserInfo WHERE @@@WHERE@@@";
            else if (hasOrderby)
                sql = "SELECT * FROM T_UserInfo ORDER BY @@@ORDERBY@@@";
            else
                sql = "SELECT * FROM T_UserInfo";
            var cmd = CommandBuilder.Build(sql, pcp, ocp);
            return CBO.FillCollection<UserInfo>(Database.ExecuteReader(cmd));
        }

        public static List<UserInfo> GetList(
            params Expression<Func<UserInfo, bool>>[] predicate)
        {
            return GetList(predicate, null);
        }

        public static List<UserInfo> GetList(
            params Expression<Func<UserInfo, Orderby>>[] orderby)
        {
            return GetList(null, orderby);
        }
        public static List<UserInfo> GetList()
        {
            return GetList((IEnumerable<Expression<Func<UserInfo, bool>>>)null, (IEnumerable<Expression<Func<UserInfo, Orderby>>>)null);
        }
        public static List<UserInfo> GetPage(
            IEnumerable<Expression<Func<UserInfo, bool>>> predicate,
            IEnumerable<Expression<Func<UserInfo, Orderby>>> orderby,
            int pageIndex, int pageSize)
        {
            if (orderby.IsEmpty())
                throw new ArgumentException(new ArgumentException().Message, "orderby");
            OrderbyCommandParameters ocp = new OrderbyCommandParameters("ORDERBY", orderby);
            PredicateCommandParameters pcp;
            string sql;
            bool hasPredicate = !predicate.IsEmpty();
            if (hasPredicate)
            {
                sql = "SELECT TOP (@PageSize) * FROM("
                    + "    SELECT *, ROW_NUMBER() OVER (ORDER BY @@@ORDERBY@@@) AS [__i_RowNumber]"
                    + "    FROM T_UserInfo WHERE @@@WHERE@@@"
                    + ") t WHERE t.[__i_RowNumber] > @PageStart";
                pcp = new PredicateCommandParameters("WHERE", predicate);
            }
            else
            {
                sql = "SELECT TOP (@PageSize) * FROM("
                    + "    SELECT *, ROW_NUMBER() OVER (ORDER BY @@@ORDERBY@@@) AS [__i_RowNumber]"
                    + "    FROM T_UserInfo"
                    + ") t WHERE t.[__i_RowNumber] > @PageStart";
                pcp = null;
            }
            var cmd = CommandBuilder.Build(sql, pcp, ocp);
            cmd.Parameters.Add(Database.CreateInputParameter("@PageSize", pageSize));
            cmd.Parameters.Add(Database.CreateInputParameter("@PageStart", pageSize * pageIndex));
            return CBO.FillCollection<UserInfo>(Database.ExecuteReader(cmd));
        }
        public static List<UserInfo> GetPage(
            int pageIndex, int pageSize,
            params Expression<Func<UserInfo, Orderby>>[] orderby)
        {
            return GetPage(null, orderby, pageIndex, pageSize);
        }
        public static int GetCount(IEnumerable<Expression<Func<UserInfo, bool>>> predicate)
        {
            bool hasPredicate = !predicate.IsEmpty();
            string sql;
            PredicateCommandParameters pcp;
            if (hasPredicate)
            {
                sql = "SELECT Count(*) FROM T_UserInfo WHERE @@@WHERE@@@";
                pcp = new PredicateCommandParameters("WHERE", predicate);
            }
            else
            {
                sql = "SELECT Count(*) FROM T_UserInfo";
                pcp = null;
            }
            var cmd = CommandBuilder.Build(sql, pcp);
            return (int)Database.ExecuteScalar(cmd);
        }
        
        public static int GetCount(params Expression<Func<UserInfo, bool>>[] predicate)
        {
            return GetCount((IEnumerable<Expression<Func<UserInfo, bool>>>)predicate);
        }
        public static UserInfo Get(IEnumerable<Expression<Func<UserInfo, bool>>> predicate)
        {
            string sql = "SELECT TOP 1 * FROM T_UserInfo WHERE @@@WHERE@@@";
            var pcp = new PredicateCommandParameters("WHERE", predicate);
            var cmd = CommandBuilder.Build(sql, pcp);
            return CBO.FillObject<UserInfo>(Database.ExecuteReader(cmd));
        }
        public static UserInfo Get(params Expression<Func<UserInfo, bool>>[] predicate)
        {
            return Get((IEnumerable<Expression<Func<UserInfo, bool>>>)predicate);
        }
        public static bool Any(IEnumerable<Expression<Func<UserInfo, bool>>> predicate)
        {
            string sql = "IF EXISTS(SELECT * FROM T_UserInfo WHERE @@@WHERE@@@) SET @Return = 1";
            var pcp = new PredicateCommandParameters("WHERE", predicate);
            var cmd = CommandBuilder.Build(sql, pcp);
            var ret = Database.CreateParameter();
            ret.ParameterName = "Return";
            ret.Value = false;
            ret.DbType = System.Data.DbType.Boolean;
            ret.Direction = System.Data.ParameterDirection.InputOutput;
            cmd.Parameters.Add(ret);
            Database.ExecuteNonQuery(cmd);
            return (bool)ret.Value;
        }
        public static bool Any(params Expression<Func<UserInfo, bool>>[] predicate)
        {
            return Any((IEnumerable<Expression<Func<UserInfo, bool>>>)predicate);
        }
        public static int Update(IEnumerable<Expression<Func<UserInfo, bool>>> predicate,
            IEnumerable<Expression<Func<UserInfo, Update>>> columns)
        {
            string sql;
            PredicateCommandParameters pcp;
            if (predicate.IsEmpty())
            {
                sql = "UPDATE T_UserInfo SET @@@UPDATE@@@";
                pcp = null;
            }
            else
            {
                sql = "UPDATE T_UserInfo SET @@@UPDATE@@@ WHERE @@@WHERE@@@";
                pcp = new PredicateCommandParameters("WHERE", predicate);
            }
            var ucp = new UpdateCommandParameters("UPDATE", columns);
            var cmd = CommandBuilder.Build(sql, ucp, pcp);
            return Database.ExecuteNonQuery(cmd);
        }
        public static int Update(Expression<Func<UserInfo, bool>> predicate,
            params Expression<Func<UserInfo, Update>>[] columns)
        {
            return Update(new Expression<Func<UserInfo, bool>>[] { predicate }, columns);
        }
        public static void Update(UserInfo info)
        {
            Update(
            x => x.UserId == info.UserId,
                x => x.UserName.Update(info.UserName)
                );
        }
        public static void Insert(IEnumerable<Expression<Func<UserInfo, Update>>> columns)
        {
            string sql = "INSERT INTO T_UserInfo (@@@INSERT.COL@@@) VALUES (@@@INSERT.VAL@@@)SELECT SCOPE_IDENTITY()";
            InsertCommandParameters icp = new InsertCommandParameters("INSERT", columns);
            var cmd = CommandBuilder.Build(sql, icp);
            Database.ExecuteScalar(cmd);
        }
        public static void Insert(params Expression<Func<UserInfo, Update>>[] columns)
        {
            Insert((IEnumerable<Expression<Func<UserInfo, Update>>>)columns);
        }
        public static void Insert(UserInfo info)
        {
            Insert(
                x => x.UserId.Update(info.UserId),
                x => x.UserName.Update(info.UserName)
                );
        }
        public static int Delete(IEnumerable<Expression<Func<UserInfo, bool>>> predicate)
        {
            if (predicate.IsEmpty())
                return Database.ExecuteNonQuery("DELETE T_UserInfo");
            PredicateCommandParameters pcp = new PredicateCommandParameters("WHERE", predicate);
            string sql = "DELETE T_UserInfo WHERE @@@WHERE@@@";
            var cmd = CommandBuilder.Build(sql, pcp);
            return Database.ExecuteNonQuery(cmd);
        }
        public static int Delete(params Expression<Func<UserInfo, bool>>[] predicate)
        {
            return Delete((IEnumerable<Expression<Func<UserInfo, bool>>>)predicate);
        }
    }
}
